﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyExceptions;
using DataModels;

namespace DALManager
{

    /// <summary>
    /// this class is used for CRUD operations + reports generation by query
    /// </summary>
    public class DAL
    {
        #region UserActions

        /// <summary>
        /// gets user from DB
        /// </summary>
        /// <param name="Username"></param>
        /// <returns></returns>
        public User GetUser(String Username)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {

                try
                {
                    context.ContextOptions.ProxyCreationEnabled = false;
                    var getUserQuery = (from user in context.Users
                                        where user.Username == Username
                                        select user).FirstOrDefault();
                    return getUserQuery;
                }
                catch (Exception ex)
                {
                    throw new UserNotFoundException("User not found" + ex.Message);
                }
            }
        }

        /// <summary>
        /// inserts user to db
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool InsertUser(User user)
        {
            bool Result = false;
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                if (user != null)
                {
                    context.AddToUsers(user);
                    context.SaveChanges();
                    Result = true;
                }
            }

            return Result;
        }

        /// <summary>
        /// returns the user, null instead of exception and null's the income/outcome to avoid serialization for the navigation
        /// properties
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public User GetUserData(string username)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var user = (from _user in context.Users
                           where _user.Username == username
                           select _user).FirstOrDefault();

                //context.Detach(user);
                if (user != null)
                {
                    user.Incomes = null;
                    user.Outcomes = null;
                }
                return user;
            }
        }

        /// <summary>
        /// updates user.
        /// </summary>
        /// <param name="user"></param>
        public void UpdateUser(User user)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                if (user != null)
                {
                    var getUserQuery = (from userDB in context.Users
                                        where userDB.Id == user.Id
                                        select userDB).FirstOrDefault();

                    if (getUserQuery != null)
                    {
                        getUserQuery.Username = user.Username;
                        getUserQuery.Password = user.Password;
                        getUserQuery.UserTypeId = user.UserTypeId;
                        context.SaveChanges();
                    }
                }
            }
        }

        public void DeleteUserByID(int userID)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                try
                {
                    var getUserQuery = (from userDB in context.Users
                                        where userDB.Id == userID
                                        select userDB).First();


                    context.Users.DeleteObject(getUserQuery);
                    context.SaveChanges();
                }
                catch (UserNotFoundException userEx) // if no user was found for deletion
                {
                    throw new UserNotFoundException(userEx.Message);
                }

            }
        }

        /// <summary>
        /// deletes user by username
        /// </summary>
        /// <param name="Username"></param>
        public void DeleteUserByUsername(String Username)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var getUserQuery = (from userDB in context.Users
                                    where userDB.Username == Username
                                    select userDB).First();

                context.Users.DeleteObject(getUserQuery);
                context.SaveChanges();
            }
        }

        #endregion //UserTypesActions

        #region Incomes Outcomes
        #region GetForUser

        //todo - ObservableCollection
        public List<Income> GetIncomesForUsers(String user)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var quser = GetUser(user);
                if (quser != null)
                {
                    var query = (from income in context.Incomes
                                 where income.UsersId == quser.Id
                                 select income).ToList();
                    return query;
                }
                else
                    return null;
            }
        }

        public List<Income> GetIncomesForUserByDateRange(String user,DateTime dateFrom, DateTime dateTo)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var quser = GetUser(user);
                if (quser != null)
                {
                    var query = (from _income in context.Incomes
                                 where _income.UsersId == quser.Id &&
                                ((_income.Date >= dateFrom) && (_income.Date <= dateTo))
                                 select _income).ToList();
                    return query;
                }
                else
                    return null;
            }
        }

        public List<Outcome> GetOutcomesForUsers(String user)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var quser = GetUser(user);
                if (quser != null)
                {
                    var query = (from outcome in context.Outcomes
                                 where outcome.UsersId == quser.Id
                                 select outcome).ToList();
                    return query;
                }
                else
                    return null;
            }
        }

        public List<Outcome> GetOutcomesForUserByDateRange(String user, DateTime dateFrom, DateTime dateTo)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var quser = GetUser(user);
                if (quser != null)
                {
                    var query = (from _outcome in context.Outcomes
                                 where _outcome.UsersId == quser.Id &&
                                ((_outcome.Date >= dateFrom) && (_outcome.Date <= dateTo))
                                 select _outcome).ToList();
                    return query;
                }
                else
                    return null;
            }
        }


        #endregion #region GetForUser

        #region Insert
        public int InsertIncome(Income income)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                if (income != null)
                {
                    context.AddToIncomes(income);
                    context.SaveChanges();
                    return income.Id;
                }
                return 0;
            }
        }

        public int InsertOutcome(Outcome outcome)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                if (outcome != null)
                {
                    context.AddToOutcomes(outcome);
                    context.SaveChanges();
                    return outcome.Id;
                }

                return 0;
            }
        }

        #endregion inserts

        #region update

        public void UpdateOutcome(Outcome outcome)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                if (outcome != null)
                {
                    var query = (from _outcome in context.Outcomes
                                 where _outcome.Id == outcome.Id
                                 select _outcome).First();

                    query.Amount = outcome.Amount;
                    query.Comment = outcome.Comment;
                    query.Date = outcome.Date;
                    query.ActionTypeId = outcome.ActionTypeId;
                    context.SaveChanges();
                }
            }
        }

        public void UpdateIncome(Income income)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                if (income != null)
                {
                    var query = (from _income in context.Incomes
                                 where _income.Id == income.Id
                                 select _income).First();

                    
                    query.Amount = income.Amount;
                    query.Comment = income.Comment;
                    query.Date = income.Date;
                    query.ActionTypeId = income.ActionTypeId;
                    context.SaveChanges();
                }
            }
        }

        #endregion update

        #region Delete

        public void DeleteOutcome(int outcomeID)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                try
                {
                    var query = (from income in context.Outcomes
                                 where income.Id == outcomeID
                                 select income).First();

                    context.DeleteObject(query);
                    context.SaveChanges();
                }
                catch (Exception ex)
                {
                    throw new Exception("no such outcome, delete failed " + ex.Message);
                }
            }
        }

        public void DeleteIncome(int incomeID)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                try
                {
                    var query = (from income in context.Incomes
                                 where income.Id == incomeID
                                 select income).First();

                    context.DeleteObject(query);
                    context.SaveChanges();
                }
                catch (Exception ex)
                {
                    throw new Exception("no such income, delete failed " + ex.Message);
                }
            }
        }
        #endregion Delete

        #endregion //Incomes Outcomes

        public List<UserType> GetUserTypes()
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var getUserQuery = (from usertp in context.UserTypes
                                    select usertp).ToList();
                return getUserQuery;
            }
        }


        public List<ActionType> GetActionTypes()
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var getActionsQuery = (from acttp in context.ActionTypes
                                       select acttp).ToList();
                return getActionsQuery;
            }
        }

        /// <summary>
        /// given a type of action and it's ID returns if its from that type of not
        /// </summary>
        /// <param name="typeOfAction"></param>
        /// <param name="actionTypeId"></param>
        /// <returns></returns>
        public bool IsActionOfType(string typeOfAction, int actionTypeId)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var query = (from acttp in context.ActionTypes
                             where ( (acttp.Kind == typeOfAction)  && ( acttp.Id == actionTypeId ))
                             select acttp).FirstOrDefault();

                if (query != null) // if such a combination exists
                { 
                    return true;
                }
                else
                {
                    return false;
                }
                


            }
        }

        public List<ActionType> GetIncomeActionTypes()
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                try
                {
                    var getActionsQuery = (from acttp in context.ActionTypes
                                           where acttp.Kind == "Income"
                                           select acttp).ToList();
                    return getActionsQuery;
                }
                catch (Exception ex)
                {
                    throw new IncomeActionNotFoundException(ex.Message);
                }

            }
        }

        public List<ActionType> GetOutcomeActionTypes()
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                try
                {
                    var getActionsQuery = (from acttp in context.ActionTypes
                                           where acttp.Kind == "Outcome"
                                           select acttp).ToList();
                    return getActionsQuery;
                }
                catch ( Exception ex )
                {
                    throw new OutcomeActionNotFoundException(ex.Message);
                }
            }
            
        }

        /// <summary>
        /// generates report from query, between dates, by sections of incomes
        /// </summary>
        /// <param name="Username"></param>
        /// <param name="DateFrom"></param>
        /// <param name="DateTo"></param>
        /// <returns></returns>
        public IEnumerable<Report> GetIncomesBySections(String Username, DateTime DateFrom, DateTime DateTo)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var query = from _income in context.Incomes.Include("ActionType")
                            where _income.User.Username == Username &&
                                    ( ( _income.Date >= DateFrom ) && ( _income.Date <= DateTo ) )
                            group _income by _income.ActionType.Type into g
                            select new Report
                            {
                                AType = g.Key,
                                SumOfAmount = g.Sum(_income => _income.Amount)
                            };

                return query.ToList();
            }
        }

        /// <summary>
        /// generates report from query, between dates, by sections of outcomes
        /// </summary>
        /// <param name="Username"></param>
        /// <param name="DateFrom"></param>
        /// <param name="DateTo"></param>
        /// <returns></returns>
        public IEnumerable<Report> GetOutcomesBySections(String Username, DateTime DateFrom, DateTime DateTo)
        {
            using (HouseKeepingContext context = new HouseKeepingContext())
            {
                var query = from _outcome in context.Outcomes.Include("ActionType")
                            where _outcome.User.Username == Username &&
                            ((_outcome.Date >= DateFrom) && (_outcome.Date <= DateTo))
                            group _outcome by _outcome.ActionType.Type into g
                            select new Report
                            {
                                AType = g.Key,
                                SumOfAmount = g.Sum(_outcome => _outcome.Amount)
                            };

                return query.ToList();
            }
        }
    }
}
